package com.baichang.taoshunda.dao.impl;

import com.baichang.framework.dao.impl.BaseDaoImpl;
import com.baichang.framework.model.Page;
import com.baichang.taoshunda.dao.OrderDao;
import com.baichang.taoshunda.entity.Order;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class OrderDaoImpl extends BaseDaoImpl<Order> implements OrderDao {
    /**
     * 用户订单列表
     *
     * @param id
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page queryOrderList(Integer id, int nowPage, int pageSize) {
        String sql = "SELECT tr.order_number AS orderNumber,tu.nick_name AS nickName,tb.buss_name AS bussName,tr.goods_all_money AS goodsMoney,tr.order_name AS orderName,tr.order_state AS orderState,tr.pay_type AS payType,tr.created " +
                "FROM t_user tu INNER JOIN t_order tr ON tu.id = tr.buyer_id INNER JOIN t_bussiness tb ON tr.seller_id=tb.id WHERE tu.user_state IN (1,2) AND tb.state IN (1,2) AND tr.order_state IN (1,2,3,4,5,6,7) AND tr.buyer_id=? and order_type!=2 ORDER BY tr.created DESC";
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    /**
     * 统计当前商家的当前月售
     *
     * @param bussId 商家编号
     * @return
     */
    @Override
    public int getCountByMouthAndBusssId(int bussId) {
        String sql = "SELECT IFNULL(SUM(( SELECT SUM(od.count) FROM t_order_details od WHERE od.order_number = o.order_number )), 0) AS count FROM t_order o WHERE o.seller_id = ? AND order_state IN (5, 6, 9, 13, 14, 15, 16) AND DATE_FORMAT(created, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') ;";
        return queryForInt(sql, bussId);
    }

    /**
     * 根据状态获取商家订单
     *
     * @param bussId     商家编号
     * @param orderState 订单状态
     *                   2：待接单
     *                   3：待配送
     *                   4：退款/售后
     *                   5：进行中
     *                   6：已完成
     *                   10：配送中
     *                   0：全部
     * @param date       日期
     * @param endDate
     * @param nowPage
     * @param pageSize   @return
     */
    @Override
    public List<Map<String, Object>> findBusinessOrderByState(Integer bussId, Integer orderState, String date, String endDate, Integer nowPage, int pageSize) {
        List<Object> objs = new ArrayList<Object>();
        String sql = " SELECT od.id,od.company_is_sure,od.refunds,od.sub_money,od.buss_get_money bussGetMoney,us.head_pic headPic,od.invoice_head,od.invoice_number ,od.red_packet_money,od.return_money,od.dispatching ,od.order_type ,od.goods_all_money,(SELECT COUNT(0) FROM t_order_state nd WHERE nd.order_id=od.id AND nd.order_state=4 ) extraction,od.order_state orderState,od.back_order_state backOrderState,od.name nickName,od.receiver_phone receiverPhone,od.goods_adress goodsAddress,od.distance,od.reason_img,od.reason_refund,od.all_money allMoney,od.remark,od.created,od.sub_money,od.order_number orderNumber,IFNULL (od.planned_delivery_time,0) plannedDeliveryTime,dc.account deliveryAccount,dc.name deliveryName,dc.head_pic deliveryHeadPic,(SELECT COUNT(os.id) FROM t_order_state os WHERE os.order_id=od.id AND os.order_state=4) isReturnGoods  FROM t_order od INNER JOIN t_user us ON od.buyer_id=us.id LEFT JOIN t_delivery_clerk dc ON od.dis_id=dc.id WHERE od.seller_id=? and order_type!=2 AND od.company_status=1";//AND od.order_state!=1
        objs.add(bussId);
        if (orderState != 0) {
            if (orderState == 5) {
                sql += " AND od.order_state IN (3,4,12) AND od.back_order_state=0 ";
            } else if (orderState == 4) {
//                sql += " AND od.order_state IN(7,9,10) ";
                sql += " AND (od.back_order_state!=0 or od.order_state IN(13,14,15,9,17,18,19)) ";
            } else if (orderState == 6) {
                sql += " AND (od.back_order_state=0 and od.order_state IN(6 ,5,18,19)) ";
            } else {
                sql += " AND od.order_state=? AND od.back_order_state=0 ";
                objs.add(orderState);
            }
        }
        if (StringUtils.isNotEmpty(date)) {
            if (orderState == 4) {
                sql += " AND DATE_FORMAT(od.apply_return_time,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d') AND DATE_FORMAT(od.apply_return_time,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') ";
                objs.add(date);
                objs.add(endDate);
            } else {
                sql += " AND DATE_FORMAT(od.created,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d') AND DATE_FORMAT(od.created,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') ";
                objs.add(date);
                objs.add(endDate);
            }
        }
        sql += " ORDER BY od.created DESC ";
        return listForMapList(sql, nowPage, pageSize, objs.toArray());
    }

    /**
     * 根据订单编号获取订单信息
     *
     * @param orderNumber 订单编号
     * @return
     */
    @Override
    public Order getOrderByOrderNumber(String orderNumber) {
        String sql = " SELECT * FROM {table} WHERE order_number=? ";
        return queryForBean(sql, orderNumber);
    }

    /**
     * 修改订单状态
     *
     * @param orderNumber 订单编号
     * @param orderState  订单状态
     * @return
     */
    @Override
    public int updateOrderStateByOrderNumber(String orderNumber, Integer orderState) {
        String sql = " UPDATE {table} SET order_state=? WHERE order_number=? ";
        return update(sql, orderState, orderNumber);
    }

    /**
     * 查询订单
     * 武圣路
     * 接口
     * 用户端
     *
     * @param orderState 订单状态
     * @param userId     用户编号
     * @param nowPage    当前页
     * @param pageSize   条数
     *                   1：待支付
     *                   2：待评价
     *                   3：待配送
     *                   4：退款/售后
     *                   0：全部
     * @return
     */
    @Override
    public List<Map<String, Object>> getOrderList(Integer orderState, Integer userId, Integer nowPage, Integer pageSize) {
        String sql = "SELECT o.id, o.order_number,o.back_order_state, o.buyer_id, o.return_money,o.red_packet_money," +
                "b.ry_token ryToken,o.dispatching,o.fee, o.sub_money, o.seller_id, o.refunds,o.dis_id, o.order_state,o.remark, " +
                "o.all_money,o.barter_img,o.barter_reason,o.order_type, o.created, b.buss_name orderName, b.head_pic as headPic,(SELECT count(od.goods_id) " +
                "FROM t_order_details od WHERE od.order_number=o.order_number) AS number FROM t_order o " +
                "INNER JOIN t_bussiness b ON b.id=o.seller_id WHERE buyer_id=? and user_status=1 and order_type!=2 ";
        if (orderState == 1) {
            sql += "AND order_state=1";
        } else if (orderState == 3) {
            sql += "AND order_state in (3,4)";
        } else if (orderState == 2) {
            sql += "AND order_state=5";
        } else if (orderState == 4) {
            sql += "AND (order_state in(7,8,9,10,13,14,15,17,18,19) OR back_order_state=1)";
        }
        sql += "  ORDER BY  o.id DESC ";
        return listForMapList(sql, nowPage, pageSize, userId);
    }

    /**
     * 查询订单
     *
     * @param name
     * @param nameUser
     * @param orderNumber
     * @param orderState
     * @param nowPage
     * @param pageSize    @return
     */
    @Override
    public Page queryOrder(String name, String nameUser, String orderNumber, Integer orderState, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = "SELECT * FROM (SELECT tr.*,tu.nick_name AS nickName,tu.account,tb.buss_name AS busName,tb.account AS baccount,dc.name AS deliveryName,dc.account AS daccount,od.goods_id AS goodsId,tg.stock,od.count FROM " +
                "t_order tr LEFT JOIN t_user tu ON tr.buyer_id=tu.id LEFT JOIN t_bussiness tb ON tr.seller_id=tb.id LEFT JOIN t_delivery_clerk dc ON tr.dis_id=dc.id LEFT JOIN t_order_details od ON tr.order_number=od.order_number LEFT JOIN t_goods tg ON od.goods_id=tg.id " +
                "WHERE tr.order_state IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) and order_type!=2 ";
        if (StringUtils.isNotBlank(name)) {
            sql += " AND tb.buss_name LIKE ?";
            list.add("%" + name + "%");
        }
        if (StringUtils.isNotBlank(nameUser)) {
            sql += " AND tu.nick_name LIKE ?";
            list.add("%" + nameUser + "%");
        }
        if (StringUtils.isNotBlank(orderNumber)) {
            sql += " AND tr.order_number LIKE ?";
            list.add("%" + orderNumber + "%");
        }
        if (orderState > 0) {
            sql += " AND tr.order_state=?";
            list.add(orderState);
        }
        sql += " GROUP BY tr.order_number ORDER BY tr.created DESC) AS aa";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 查询订单详细
     *
     * @param orderNumber
     * @param nowPage
     * @param pageSize    @return
     */
    @Override
    public Page<Map<String, Object>> queryOrderDetail(String orderNumber, int nowPage, int pageSize) {
        String sql = "SELECT td.*,tg.head_pic AS headPic FROM t_order_details td INNER JOIN t_goods tg ON td.goods_id = tg.id WHERE td.order_number=?";
        sql += " ORDER BY td.created DESC ";
        return pageForMapList(sql, nowPage, pageSize, orderNumber);
    }

    /**
     * 拒绝退款
     *
     * @param id
     * @param state
     * @param reason
     * @return
     */
    @Override
    public int updateState(Integer id, Integer state, String reason) {
        String sql = "UPDATE t_order SET order_state=?,reason=? WHERE id=?";
        return update(sql, state, reason, id);
    }

    /**
     * 获取订单信息
     *
     * @param id
     * @return
     */
    @Override
    public Order getOrderMoney(Integer id) {
        String sql = "SELECT * FROM t_order WHERE id=?";
        return queryForBean(sql, id);
    }

    /**
     * 同意退货
     *
     * @param id
     * @param state
     * @return
     */
    @Override
    public int updateOrderState(Integer id, Integer state) {
        String sql = "UPDATE t_order SET order_state=? WHERE id=?";
        return update(sql, state, id);
    }

    /**
     * 取消订单
     *
     * @param orderNumber 订单单号
     * @return
     */
    @Override
    public boolean cancelOrder(String orderNumber) {
        String sql = "UPDATE t_order  set order_state=11 WHERE order_number=? AND order_state<3";
        return update(sql, orderNumber) > 0;
    }

    @Override
    public void updateOrderRemark(String remark, Integer id) {
        String sql = "UPDATE `t_order` SET `remark`=? WHERE (`id`=?)";
        update(sql, remark, id);
    }

    @Override
    public int updateOrderPayOrderKid(Integer id, String newPayOrderKid, int payMethod) {
        String sql = "UPDATE `t_order` SET `pay_orderkid`=?,pay_type=? WHERE (`id`=?)";
        return update(sql, newPayOrderKid, payMethod, id);
    }

    @Override
    public void updateOrderRefunds(int orderId) {
        String sql = "UPDATE `t_order` SET `refunds`='1' WHERE (`id`=?)";
        update(sql, orderId);

    }


    /**
     * 获取商家今天收入
     *
     * @param bussId      商家编号
     * @param currentDate 当前日期
     * @return
     */
    @Override
    public Map<String, Object> getBussinessTodayMoney(Integer bussId, String currentDate) {
//        String sql = " SELECT SUM(od.all_money) allMoney FROM t_order od WHERE od.seller_id=? AND od.created LIKE ? AND od.order_state IN (3,4,5,6) ";
        String sql = " SELECT IFNULL(SUM(balance), 0) - ( SELECT IFNULL(SUM(balance), 0) FROM t_company_balance WHERE (buss_id = ? AND TO_DAYS(created) = TO_DAYS(?) AND source_type = 2) ) AS allMoney FROM t_company_balance WHERE (buss_id = ? AND TO_DAYS(created) = TO_DAYS(?) AND source_type = 1) ";
        return queryForMap(sql, bussId, currentDate, bussId, currentDate);
    }

    /**
     * 获取商家今天的订单数
     *
     * @param bussId      商家编号
     * @param currentDate 当前日期
     * @return
     */
    @Override
    public Map<String, Object> getBussinessTodayOrderNum(Integer bussId, String currentDate) {
//        String sql = " SELECT COUNT(*) todayOrderNum FROM t_order od WHERE od.seller_id=? AND od.created LIKE ? AND od.order_state IN (3,4,5,6) ";
//        String sql = " SELECT COUNT(*) todayOrderNum FROM t_order od WHERE od.seller_id=? AND od.created LIKE ? AND od.order_state IN (5,6) ";
        String sql = " SELECT COUNT(0) todayOrderNum FROM t_company_balance WHERE buss_id=? and TO_DAYS(created)=TO_DAYS(?)  AND source_type!=3";
        return queryForMap(sql, bussId, currentDate);
    }

    /**
     * 查询当前订单用户信息
     *
     * @param orderNumber
     * @return
     */
    @Override
    public Map<String, Object> getOrderBuyerIdInfo(String orderNumber) {
        String sql = "SELECT tu.* FROM t_order tr INNER JOIN t_user tu ON tr.buyer_id=tu.id WHERE tr.order_number=?";
        return queryForMap(sql, orderNumber);
    }

    /**
     * 获取订单id
     *
     * @param orderNumber
     * @return
     */
    @Override
    public Order getOrderInfo(String orderNumber) {
        String sql = " SELECT id FROM t_order WHERE order_number=? ";
        return queryForBean(sql, orderNumber);
    }

    /**
     * 配送员根据订单状态获取列表
     *
     * @param orderState 订单状态
     * @param maxlng     最大经度
     * @param minlng     最小经度
     * @param maxlat     最大纬度
     * @param minlat     最小纬度
     * @param disId      配送员编号
     * @param nowPage
     * @param pageSize   @return
     */
    @Override
    public List<Map<String, Object>> getOrderListByOrderState(int orderState, String maxlng, String minlng, String maxlat, String minlat, int disId, int nowPage, int pageSize) {
        List<Object> list = new ArrayList<Object>();
        String sql = "SELECT * FROM ( SELECT o.id, o.order_state AS orderState, o.remark, o.sub_money , ( SELECT COUNT(order_id) AS isReturnGoods FROM t_order_state WHERE order_id = o.id AND order_state = 4 ) AS isReturnGoods, o.order_number, o.company_is_sure, o.dispatching AS dispatching, o.fee , IFNULL(o.planned_delivery_time, 0) AS isImmediate, o.ulat , o.ulng, b.lng, b.lat, o.dis_id AS disId, b.buss_name ,o.order_type orderType, o.receiver_phone, b.buss_tel, b.adress AS bussAddress, o.goods_adress , ( SELECT COUNT(id) FROM t_reminder r WHERE r.order_id = o.id ORDER BY r.id DESC ) AS isReminder,o.created, IFNULL(( SELECT state FROM t_reminder r WHERE r.order_id = o.id ORDER BY r.id DESC LIMIT 1 ), 0) AS isReminderState FROM t_order o INNER JOIN t_bussiness b ON b.id = o.seller_id WHERE  o.company_is_sure != 2 ORDER BY o.created DESC ) orders ";
        sql += " where orders.orderType != 2  ";
        if (orderState == 4) {
            sql += " AND  ((orders.orderState = 9  AND orders.isReturnGoods>0 ) OR orders.orderState = 4) ";
        } else if (orderState == 3) {
            sql += "  AND orders.orderState = ? AND orders.lng <= ? AND orders.lng >= ? AND orders.lat >= ? AND orders.lat <= ? ";
            list.add(orderState);
            list.add(maxlng);
            list.add(minlng);
            list.add(minlat);
            list.add(maxlat);
        } else {
            sql += " and  orders.orderState = ? ";
            list.add(orderState);
        }
        if (disId != 0) {
            sql += " and orders.disId=? ";
            list.add(disId);
        }

        sql += " ORDER BY orders.created DESC limit ?,?";
        list.add((nowPage - 1) * pageSize);
        list.add(pageSize);
        List newlist = null;
        newlist = this.jdbcTemplate.query(sql, list.toArray(), new RowMapper() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                return toMap(resultSet);
            }
        });

        return newlist;
    }

    @Override
    public boolean updateDeliveryIdByOrderId(int orderId, int disId) {
        String sql = "UPDATE `t_order` SET `dis_id`=? ,send_time=now() WHERE id=?";
        return update(sql, disId, orderId) > 0;
    }

    @Override
    public Order getOrderByPayOrderNumber(String orderNumber) {
        String sql = " SELECT * FROM {table} WHERE pay_orderkid=? ";
        return queryForBean(sql, orderNumber);
    }

    /**
     * 查询用户订单状态
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> getOrderByUserInfo(Integer id) {
        String sql = "SELECT tr.order_state AS orderState FROM t_order tr INNER JOIN t_user tu ON tr.buyer_id=tu.id WHERE tr.buyer_id=?";
        return queryForMapList(sql, id);
    }

    @Override
    public int updateOrderReason(Integer id, String reason) {
        String sql = " UPDATE {table} SET reason=? WHERE id=? ";
        return update(sql, reason, id);
    }

    @Override
    public List<Map<String, Object>> getOrderByIdAndTime(int orderState, int disId, int nowPage, int pageSize,
                                                         String starTime, String endTime) {

        String sql = "SELECT o.id,o.order_state orderState,o.created,o.end_time,o.sub_money,o.remark,o.back_order_state," +
                " o.order_number,o.red_packet_money,o.dispatching AS dispatching, o.fee," +
                " IFNULL(o.planned_delivery_time, 0) AS isImmediate, o.ulat , o.ulng, b.lng, b.lat, b.buss_name," +
                " o.receiver_phone , b.buss_tel, b.adress AS bussAddress, o.goods_adress, " +
                "( SELECT COUNT(id) FROM t_reminder r WHERE r.order_id = o.id and state=1 order by r.id desc) AS isReminder," +
                "( SELECT COUNT(id) FROM t_reminder r WHERE r.order_id = o.id  )  AS isReminderState FROM t_order o " +
                "INNER JOIN t_bussiness b ON b.id = o.seller_id where dis_id=? and o.order_state in (5,6,9,13,14,15,16) and order_type!=2 ";
        List list = new ArrayList();
        list.add(disId);
        if (!starTime.isEmpty() || !starTime.equals("")) {
            sql += " and  TO_DAYS(o.end_time)>=TO_DAYS(?) and  TO_DAYS(o.end_time)<=TO_DAYS(?) ";
            list.add(starTime);
            list.add(endTime);
        }
        sql += " ORDER BY o.end_time DESC";
        return listForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 查询配送员订单
     *
     * @param deliveryId 配送员编号
     * @param state      订单状态
     *                   1：未完成
     * @return
     */
    @Override
    public List<Map<String, Object>> findDeliveryOrderByState(Integer deliveryId, int state) {
        List<Object> objs = new ArrayList<Object>();
        String sql = " SELECT * FROM t_order WHERE dis_id=?  ";
        objs.add(deliveryId);
        if (state == 1) {
            sql += " AND order_state IN (4,12) ";
        }
        sql += " ORDER BY created DESC ";
        return queryForMapList(sql, objs.toArray());
    }

    @Override
    public boolean updateOrderApplyTime(Integer id) {
        String sql = "UPDATE t_order set apply_return_time=NOW() WHERE id=?";
        return update(sql, id) > 0;
    }

    /**
     * 修改订单状态
     *
     * @param orderId        订单编号
     * @param orderState     订单状态
     * @param backOrderState 订单退货/退款状态
     * @return
     */
    @Override
    public int updateOrderAllState(Integer orderId, Integer orderState, Integer backOrderState) {
        String sql = " UPDATE {table} SET order_state=?,back_order_state=? WHERE id=? ";
        return update(sql, orderState, backOrderState, orderId);
    }

    /**
     * 修改订单退款状态
     *
     * @param orderId        订单编号
     * @param backOrderState 退款状态
     * @return
     */
    @Override
    public int updateOrderBackState(Integer orderId, Integer backOrderState) {
        String sql = " UPDATE {table} SET back_order_state=? WHERE id=? ";
        return update(sql, backOrderState, orderId);
    }

    @Override
    public int getTotal(Integer orderState, Integer userId) {
        String sql = "SELECT count(0) FROM {table} ";
        sql += "WHERE buyer_id=? ";
        if (orderState == 1) {
            sql += "AND order_state=1";
        } else if (orderState == 2) {
            sql += "AND order_state=5";
        } else if (orderState == 3) {
            sql += "AND order_state in(7,8,9,10)";
        }
        sql += "  ORDER BY  id DESC ";
        return queryForInt(sql, userId);
    }

    @Override
    public void updateEndtime(Integer id) {
        String sql = "UPDATE {table} SET end_time=now() WHERE id=? ";
        update(sql, id);
    }

    @Override
    public Integer getIsReturnGoods(Integer orderId) {
        String sql = " SELECT COUNT(*) isReturnGoods  FROM t_order_state WHERE order_id=? AND order_state=4 ";
        return queryForInt(sql, orderId);
    }

    /**
     * 查询有未完成的订单或者退款、退货的订单
     *
     * @param busId
     * @return
     */
    @Override
    public Map<String, Object> getOrderNum(Integer busId) {
        String sql = "SELECT COUNT(*) AS num FROM t_order WHERE order_state NOT IN (1,5,6,9,14,16) AND seller_id = ?";
        return queryForMap(sql, busId);
    }

    /**
     * @param busId
     * @return
     */
    @Override
    public Map<String, Object> getOrderMaxCreated(Integer busId) {
        String sql = "SELECT IFNULL(MAX(created),0) AS times FROM t_order WHERE seller_id = ?";
        return queryForMap(sql, busId);
    }

    /**
     * 查询全部完成的订单数量
     *
     * @param bussId
     * @return
     */
    @Override
    public Integer getAllOrderNumber(Integer bussId) {
        String sql = " SELECT count(0) FROM t_company_balance WHERE buss_id=? ";
        return queryForInt(sql, bussId);
    }

    @Override
    public boolean removeOrderUserStatus(String orderNumber) {
        String sql = "UPDATE `t_order` SET `user_status`=0 WHERE (`order_number`=?)";
        return update(sql, orderNumber) > 0;
    }

    @Override
    public boolean updateOrderMoney(BigDecimal allMoney, Double goodsAllMoney, BigDecimal platformGetMoney, BigDecimal bussinessGetMoney, String orderNumber, BigDecimal returnMoney) {
        String sql = "UPDATE t_order SET all_money=?,goods_all_money=?,platform_get_money=?,buss_get_money=?,return_money=? where order_number=?";
        return update(sql, allMoney, goodsAllMoney, platformGetMoney, bussinessGetMoney, returnMoney, orderNumber) > 0;
    }

    @Override
    public List<Map<String, Object>> getReservationOrderList(Integer orderState, Integer userId, Integer nowPage, Integer pageSize) {

        String sql = "SELECT o.id, o.order_number,o.sub_money,o.back_order_state, o.red_packet_money,b.ry_token ryToken, o.return_money, o.is_activity, o.buyer_id,o.dispatching,o.fee, o.seller_id, o.refunds,o.dis_id, o.order_state,o.remark, o.all_money, o.created, o.order_name, b.head_pic as headPic,(SELECT count(od.goods_id) FROM t_order_details od WHERE od.order_number=o.order_number) AS number FROM t_order o INNER JOIN t_bussiness b ON b.id=o.seller_id WHERE buyer_id=? and user_status=1 and order_type=2 ";
        if (orderState == 1) {
            sql += " AND order_state=1 ";
        } else if (orderState == 2) {
            sql += " AND order_state=5 ";
        } else if (orderState == 3) {
            sql += " AND order_state=2 ";
        } else if (orderState == 4) {
            sql += " AND (order_state in(7,8,9,10,13,14,15) OR back_order_state=1) ";
        }
        sql += "  ORDER BY  o.id DESC ";
        return listForMapList(sql, nowPage, pageSize, userId);
    }

    @Override
    public List<Map<String, Object>> findBusinessReservationOrderByState(Integer bussId, Integer orderState, String date, String endDate, Integer nowPage, Integer pageSize) {
        String sql = "SELECT o.id, o.order_number, o.back_order_state, o.buyer_id, o.sub_money, o.red_packet_money,o.return_money,o.dispatching , o.fee, o.seller_id, o.refunds, o.dis_id, o.order_state , o.remark, o.goods_adress AS goodsAddress, o.receiver_phone, o.all_money, u.account AS phone , u.head_pic AS userHeadPic, u.nick_name AS userName, o.buss_get_money, o.created, o.order_name , b.head_pic AS headPic , ( SELECT COUNT(od.goods_id) FROM t_order_details od WHERE od.order_number = o.order_number ) AS number FROM t_order o INNER JOIN t_bussiness b ON b.id = o.seller_id INNER JOIN t_user u ON u.id = o.buyer_id WHERE seller_id = ? AND user_status = 1 AND order_type = 2 AND company_status = 1 ";
        List<Object> objs = new ArrayList<Object>();
        objs.add(bussId);
        if (orderState != 0) {
            if (orderState == 1) {
                sql += " AND order_state=1";
            } else if (orderState == 5) {
                sql += " AND o.order_state IN (3,4,12) AND o.back_order_state=0 ";
            } else if (orderState == 6) {
                sql += " AND o.order_state IN (5,6) AND o.back_order_state=0 ";
            } else if (orderState == 4) {
                sql += " AND (o.back_order_state!=0 or o.order_state IN(13,14,15,9)) ";
            } else {
                sql += " AND o.order_state=? AND o.back_order_state=0 ";
                objs.add(orderState);
            }
        }
        if (StringUtils.isNotEmpty(date)) {
            if (orderState == 4) {
                sql += " AND DATE_FORMAT(o.apply_return_time,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d') AND DATE_FORMAT(o.apply_return_time,'%Y-%m-%d') <=DATE_FORMAT(?,'%Y-%m-%d') ";
                objs.add(date);
                objs.add(endDate);
            } else {
                sql += " AND DATE_FORMAT(o.created,'%Y-%m-%d')>=DATE_FORMAT(?,'%Y-%m-%d')  AND DATE_FORMAT(o.created,'%Y-%m-%d')<=DATE_FORMAT(?,'%Y-%m-%d') ";
                objs.add(date);
                objs.add(endDate);
            }
        }
        sql += " ORDER BY o.created DESC ";

        return listForMapList(sql, nowPage, pageSize, objs.toArray());
    }

    @Override
    public void updateReasonImgAndReasonRefund(String reasonImg, String reasonRefund, Integer orderId) {
        String sql = "UPDATE `t_order` SET `reason_refund`=?, `reason_img`=? WHERE id=?";
        update(sql, reasonRefund, reasonImg, orderId);
    }

    /**
     * 预约订单
     *
     * @param userName
     * @param busName
     * @param nowPage
     * @param pageSize    @return
     * @param orderNumber
     * @param orderState
     */
    @Override
    public Page queryAppointmentOrder(String userName, String busName, int nowPage, int pageSize, String orderNumber, Integer orderState) {
        List list = new ArrayList();
        String sql = "SELECT tr.*,tu.nick_name AS userName,tb.buss_name AS busName FROM t_order tr INNER JOIN t_user tu ON tu.id=tr.buyer_id INNER JOIN t_bussiness tb ON tb.id=tr.seller_id  " +
                "WHERE order_type=2 ";
        if (StringUtils.isNotBlank(userName)) {
            sql += " AND tu.nick_name LIKE ?";
            list.add("%" + userName + "%");
        }
        if (StringUtils.isNotBlank(busName)) {
            sql += " AND tb.buss_name LIKE ?";
            list.add("%" + busName + "%");
        }
        if (StringUtils.isNotBlank(orderNumber)) {
            sql += " AND tr.order_number LIKE ?";
            list.add("%" + orderNumber + "%");
        }
        if (orderState > 0) {
            sql += " AND tr.order_state=?";
            list.add(orderState);
        }
        sql += " ORDER BY tr.created DESC ";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    @Override
    public boolean removeOrderCompanyStatus(String orderNumber) {
        String sql = "UPDATE `t_order` SET `company_status`=0 WHERE (`order_number`=?)";
        return update(sql, orderNumber) > 0;
    }

    @Override
    public void updateSingleReason(Integer orderId, String singleReason) {
        String sql = "UPDATE `t_order` SET `single_reason`=? WHERE id=?";
        update(sql, singleReason, orderId);
    }

    @Override
    public void updateIsHomeInstallation(int orderId) {
        String sql = "UPDATE `t_order` SET `is_home_installation`='1' WHERE (`id`=?)";
        update(sql, orderId);
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> allOrder(int type) {
        String sql = null;
        if (type == 1) {
            sql = "SELECT IFNULL(SUM(all_money),0) AS sumMoney FROM t_order WHERE order_state IN (5,6)";
        } else if (type == 2) {
            sql = "SELECT IFNULL(SUM(all_money),0) AS daySumMoney FROM t_order WHERE TO_DAYS(created) = TO_DAYS(NOW()) AND order_state IN (5,6)";
        } else if (type == 3) {
            sql = "SELECT IFNULL(SUM(return_money),0) AS dayReturnMoney FROM t_order WHERE TO_DAYS(created) = TO_DAYS(NOW()) AND order_state IN (9)";
        } else if (type == 4) {
            sql = "SELECT IFNULL(SUM(return_money),0) AS allReturnMoney FROM t_order WHERE order_state IN (9)";
        } else if (type == 5) {
            sql = "SELECT IFNULL(SUM(all_money),0) AS allMoney FROM t_order WHERE TO_DAYS(created) = TO_DAYS(NOW()) AND order_state IN (1)";
        } else if (type == 6) {
            sql = "SELECT IFNULL(SUM(all_money),0) AS allPendingMoney FROM t_order WHERE order_state IN (1)";
        }
        return queryForMap(sql);
    }

    @Override
    public void updateRedpacketMoney(String orderNumber, BigDecimal add) {
        String sql = "UPDATE `t_order` SET `red_packet_money`=? WHERE (`order_number`=?)";
        update(sql, add, orderNumber);
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> allDispatchingOrder() {
        String sql = "SELECT COUNT(dis_id) AS allDispatchingOrder FROM t_order WHERE order_state IN (5,6) AND dis_id IS NOT NULL AND order_type!=2";
        return queryForMap(sql);
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> dayDispatchingOrder() {
        String sql = "SELECT COUNT(dis_id) AS dayDispatchingOrder FROM t_order WHERE TO_DAYS(created) = TO_DAYS(NOW()) AND order_state IN (5,6) AND dis_id IS NOT NULL AND order_type!=2";
        return queryForMap(sql);
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> monthDispatchingOrder() {
        String sql = "SELECT COUNT(dis_id) AS monthDispatchingOrder FROM t_order WHERE DATE_FORMAT(created,'%Y%m') = DATE_FORMAT(CURDATE() ,'%Y%m') AND order_state IN (5,6) AND dis_id IS NOT NULL AND order_type!=2";
        return queryForMap(sql);
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> yearDispatchingOrder() {
        String sql = "SELECT COUNT(dis_id) AS yearDispatchingOrder FROM t_order WHERE YEAR(created)=YEAR(NOW()) AND order_state IN (5,6) AND dis_id IS NOT NULL AND order_type!=2";
        return queryForMap(sql);
    }

    /**
     * @param type
     * @return
     */
    @Override
    public Map<String, Object> allDealOrder(int type) {
        String sql = null;
        if (type == 1) {
            sql = "SELECT COUNT(*) AS allDealOrder FROM t_order WHERE order_state IN (5,6)";
        } else if (type == 2) {
            sql = "SELECT COUNT(*) AS dayDealOrder FROM t_order WHERE order_state IN (5,6) AND TO_DAYS(created) = TO_DAYS(NOW())";
        } else if (type == 3) {
            sql = "SELECT COUNT(*) AS monthDealOrder FROM t_order WHERE order_state IN (5,6) AND DATE_FORMAT(created,'%Y%m') = DATE_FORMAT(CURDATE() ,'%Y%m')";
        } else if (type == 4) {
            sql = "SELECT COUNT(*) AS yearDealOrder FROM t_order WHERE order_state IN (5,6) AND YEAR(created)=YEAR(NOW())";
        }
        return queryForMap(sql);
    }

    @Override
    public int getOrderCountBy(Integer userId) {
        String sql = " SELECT COUNT(0) FROM t_order WHERE seller_id=? AND order_state=2 and order_type!=2";
        return queryForInt(sql, userId);
    }

    @Override
    public void updatePayType(int payMethod, Integer id) {
        String sql = "UPDATE `t_order` SET `pay_type`=? WHERE (`id`=? )";
        update(sql, payMethod, id);
    }

    @Override
    public int getOrderCountByDeliveryId(Integer id) {
        String sql = "SELECT COUNT(id) FROM t_order WHERE dis_id=? AND order_state IN(3,4,12)";
        return queryForInt(sql, id);
    }

    @Override
    public boolean updateBarter(String barterImg, String barterRefund, Integer id) {
        String sql = "UPDATE `t_order` SET `barter_reason`=?, `barter_img`=? WHERE id=?";
        return update(sql, barterRefund, barterImg, id) > 0;
    }

    @Override
    public boolean updateCompanyIsSure(Integer orderId, int companyIsSure) {
        String sql = "UPDATE `t_order` SET `company_is_sure`=? WHERE id=?";
        return update(sql, companyIsSure, orderId) > 0;
    }
}